--Set the database to which these functions will be installed
use Lib
GO

--Enable CLR Integration if is not enabled
--exec sp_configure 'clr enabled', 1
--GO
--RECONFIGURE
--GO



--Drop the functions if they already exist
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[clr].[fnRegExIsMatch]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [clr].[fnRegExIsMatch]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[clr].[fnRegExReplace]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [clr].[fnRegExReplace]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[clr].[fnRegExMatches]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [clr].[ftRegExMatches]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[clr].[fnRegExSplit]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [clr].[ftRegExSplit]
GO

--Drop the assembly if it already exists
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SqlRegEx')
	DROP ASSEMBLY [SqlRegEx]
GO

--Create the assembly
CREATE ASSEMBLY [SqlRegEx] 
FROM 'C:\NHP_Interfaces\NHPLib\CLR\SqlRegEx.dll' 
WITH PERMISSION_SET = SAFE
GO

--Create the functions
CREATE FUNCTION [clr].[fnRegExIsMatch] (@Input NVARCHAR(MAX), @Pattern NVARCHAR(MAX), @IgnoreCase BIT)
RETURNS BIT	
AS EXTERNAL NAME SqlRegEx.[SqlClrTools.SqlRegEx].RegExIsMatch
GO

CREATE FUNCTION [clr].[fnRegExReplace] (@Input NVARCHAR(MAX), @Pattern NVARCHAR(MAX), @Replacement NVARCHAR(MAX), @IgnoreCase BIT)	
RETURNS NVARCHAR(MAX) 
AS EXTERNAL NAME SqlRegEx.[SqlClrTools.SqlRegEx].RegExReplace
GO

CREATE FUNCTION [clr].[ftRegExMatches] (@Input NVARCHAR(MAX), @Pattern NVARCHAR(MAX), @IgnoreCase BIT) 
RETURNS TABLE (Match NVARCHAR(MAX), MatchIndex INT, MatchLength INT)	
AS EXTERNAL NAME SqlRegEx.[SqlClrTools.SqlRegEx].RegExMatches
GO

CREATE FUNCTION [clr].[ftRegExSplit]   (@Input NVARCHAR(MAX), @Pattern NVARCHAR(MAX), @IgnoreCase BIT)	
RETURNS TABLE (Match NVARCHAR(MAX)) 
AS EXTERNAL NAME SqlRegEx.[SqlClrTools.SqlRegEx].RegExSplit
GO

--Execute sample cases
SELECT clr.fnRegExIsMatch('Hello World', 'w', 1)			--Ignores Case
SELECT clr.fnRegExIsMatch('Hello World', 'w', 0)			--Case Sensitive

SELECT clr.fnRegExReplace('Hello World', 'h', 'J', 1)		--Ignores Case
SELECT clr.fnRegExReplace('Hello World', 'h', 'J', 0)		--Case Sensitive

SELECT * FROM clr.ftRegExMatches('Hello World', 'L', 1)	--Ignores Case
SELECT * FROM clr.ftRegExMatches('Hello World', 'L', 0)	--Case Sensitive

SELECT * FROM clr.ftRegExSplit('Hello World', 'L', 1)		--Ignores Case
SELECT * FROM clr.ftRegExSplit('Hello World', 'L', 0)		--Case Sensitive